Schemas and Instances
The schema and instance are the essential terms related to databases. In this lesson, we will discuss the key differences between the two.
We'll cover the following
Database schema#
A schema is the blueprint of a database. The names of tables, columns of each table, datatype, functions, and other objects are included in the schema.
We use the schema diagram to display the schema of a database. The schema diagram for the university database can be seen below:
Student Table
| ID | First_Name | Last_Name | Class | Major |
|---|
Course Table
| Course_ID | Course_Name | Course_credits |
|---|
Department Table
| Department_Code | Department_Name |
|---|
Instructor Table
| Instructor_ID | Instructor_fname | Department_Code |
|---|
Grade Table
| ID | Course_ID | Grade |
|---|
It is important to note that the schema diagram is not the same thing as the schema.
The schema diagram displays only certain aspects of a schema, such as the names of record types and data items. Other features (although present in the schema) are not specified in the schema diagram; for example, the above diagram shows neither the data type of each item nor the relationships among the various tables.
On another note, the schema is not changed frequently, sometimes changes need to be applied to the schema as the requirements of the application change. For example, we may decide to add another data item to each record in a table, such as adding an Address field to the Student schema. This schema modification or alteration is known as schema evolution.
Database instance#
An instance is the information collected in a database at some specific moment in time, also known as the database state. It is a snapshot of the current state or occurrence of a database. Each time data is inserted into or deleted from the database, it changes the state of the database. That is the reason why an instance of the database changes more often.
The starting state of the database is acquired when the database is first loaded with initial data. From then onwards, each time data is updated we get a new database instance. At any point in time, there is a current state associated with a database.
To explain the concept of instances further we will look at a snapshot of the STUDENT table at a particular moment in time.
| ID | First_Name | Last_Name | Class | Major |
|---|---|---|---|---|
| 1001 | Bob | Dylan | Junior | Maths |
| 1002 | Ceaser | Zappelli | Freshman | Economics |
| 1003 | Antony | Rodgers | Senior | Psychology |
| 1004 | George | Miller | Sophomore | Computer science |
| … | … | … | … | … |
This is just one instance of the STUDENT table. If we add, remove or update records into this table than we will enter a new database state.
In the next lesson, we will dive deep into the three-schema architecture.
